
package de.dtools.n2g.sql;

/**
 *	@project:	note2go
 *	@author:	D.Toepel
 *	@version:	2009-08-04
 *	@note:		---
 *	
 *	Copyright (C) 2009  D.Toepel <de.dtools> {at} <gmail> {dot} <com>
 *	
 *	This program is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU General Public License as published by
 *	the Free Software Foundation, either version 3 of the License, or
 *	(at your option) any later version.
 *	
 *	This program is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU General Public License for more details.
 *	
 *	You should have received a copy of the GNU General Public License
 *	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *	
 */

import java.io.*;
import java.sql.*;

import javax.swing.text.BadLocationException;
import javax.swing.text.Document;
import javax.swing.text.rtf.RTFEditorKit;

import org.h2.fulltext.FullText;
import org.h2.fulltext.FullTextLucene;
import org.h2.jdbc.JdbcSQLException;
import org.h2.jdbcx.JdbcConnectionPool;

import de.dtools.n2g.core.Label;
import de.dtools.n2g.core.OptionsSet;
import de.dtools.n2g.core.User;
import de.dtools.n2g.note.EntrySet;
import de.dtools.n2g.note.EntrySet.TYPE;
import de.schlichtherle.io.FileOutputStream;

public class Database{

	// ***************************************
	// *** DECLARATIONS **********************
	// ***************************************
	
	private JdbcConnectionPool cp;
	private Statement statement;
	private DBResult dbResult;
	private String dbURL;
	private String dbUserName;
	private String dbRoot = "root";
	private String filePassWord = "geheim";
	private String readPassWord = "noch geheimer";
	private String userPassWord;
	private User currUser;
	private EntrySet dbEntrySet;
	private Label unsortLabel;


	// ***************************************
	// *** CONSTRUCTOR ***********************
	// ***************************************
	
	public Database(){
		super();
	}
	
	
	// ***************************************
	// *** PUBLIC METHODS ********************
	// ***************************************
	
	public void setDbData(Object[] args) {
		this.dbURL = (String) args[0];
		this.dbUserName = (String) args[0];
		this.userPassWord = (String) args[0];
	}

	public void closeConnection(){
		try {
			cp.dispose();
		} catch (SQLException e) {}
	}
	
	public DBResult getDBResult(){return dbResult;}
	
	public DBResult setConnection(){
		dbResult = new DBResult();
		setConnectionPool();
		Connection conn = getConnection();
		if(!dbResult.isDBExists){
			String url = "jdbc:h2:" + dbURL + ";CIPHER=AES";
			String  pword = filePassWord + " " + readPassWord;
			cp = JdbcConnectionPool.create(url, dbRoot, pword);
			createDatabase();
		}
		selectTable("SELECT PREF_USERLOCAL,PREF_ID FROM N2G_PREFERENCES;");
		return dbResult;
	}
	
	public DBResult createDatabase(){
//		setConnectionPool();
		if(cp != null){
			try {
				String[] batch = readTextFile("create.sql").split("-- GO");
				Connection conn = getConnection();
				statement = conn.createStatement();
				for (String string : batch) {
					statement.addBatch(string);
				}
				statement.executeBatch();
				conn.close();
				dbResult.isDBExists = true;
				dbResult.isPassword = true;
			} catch (Exception e) {
				dbResult.isDBExists = false;
				dbResult.setSqlComment(e.getLocalizedMessage());
			}
		}
		return dbResult;
	}
	
	public OptionsSet getOptions(){
		
		OptionsSet options = new OptionsSet(false);
		options.setLocalUser(currUser);
		unsortLabel = new Label("unsortiert",currUser.getUserID());
		String[][] result;
		
		// load preferences
		{
			selectTable("SELECT * FROM N2G_PREFERENCES WHERE " +
					"N2G_PREFERENCES.pref_ID="+currUser.getUserID());
			result = dbResult.getRowContent();
			options.setIsRoot(Boolean.parseBoolean(result[0][3]));
			options.setIsPortable(Boolean.parseBoolean(result[0][4]));
		}
		
		// load notesettings
		{
			selectTable("SELECT * FROM N2G_NOTESETTINGS WHERE " +
					"N2G_NOTESETTINGS.pref_ID="+currUser.getUserID());
			result = dbResult.getRowContent();
			options.setNsetPublic(Boolean.parseBoolean(result[0][2]));
			options.setNsetAutosync(Boolean.parseBoolean(result[0][3]));
			options.setNsetSynctime(Integer.parseInt(result[0][4]));
			options.setNsetLocal2host(Boolean.parseBoolean(result[0][5]));
			options.setNsetHost2local(Boolean.parseBoolean(result[0][6]));
		}
		
		// load serversettings
		{
			selectTable("SELECT * FROM N2G_SERVERSETTINGS WHERE " +
					"N2G_SERVERSETTINGS.pref_ID="+currUser.getUserID());
			result = dbResult.getRowContent();
			User hostUser = new User(result[0][5],Integer.parseInt(result[0][4]));
			hostUser.setPassword(result[0][6].toCharArray());
			options.setHostUser(hostUser);
			options.setServerName(result[0][2]);
			options.setServerPort(Integer.parseInt(result[0][3]));
			options.setServerAutoConnect(Boolean.parseBoolean(result[0][7]));
		}
		
		return options;
	}
	

	public Boolean setOptions(OptionsSet options){
		if(setPreferences(options)){
			if(setNoteSettings(options)){
				if(setServerSettings(options)){
					if(setUserSettings(options)){
						if(setGroupSettings(options)){
							if(setUser2Group(options)){
								return true;
							}
						}
					}
				}
			}
		}
		return false;
	}
	
	public DBResult getTagList(Label[] labels){
		if(labels == null){
			selectTable("SELECT * FROM GET_TAGLIST;",currUser.getUserID());
		}else{
			getEntryHeaders(labels);
			String[][] result = dbResult.rowContent; 
			String sql = "(";
		    String sql1 = 
				"SELECT tid, tname FROM ( " +
					"SELECT " +
						"N2G_TAGS.TAG_ID AS tid, " +
						"N2G_TAGS.TAG_NAME AS tname " +
					"FROM N2G_TAG2NOTE " +
					"LEFT JOIN N2G_TAGS ON N2G_TAG2NOTE.TAG_ID = N2G_TAGS.TAG_ID " +
					"WHERE (";
			for (int i = 0; i < result.length - 1; i++) {
				sql += sql1 +"N2G_TAG2NOTE.NOTE_ID = " + Integer.parseInt(result[i][0]) +
					") GROUP BY tname ORDER BY tname ASC) " +
					"UNION ";
			}
			sql += sql1 + "N2G_TAG2NOTE.NOTE_ID = " + 
			Integer.parseInt(result[result.length - 1][0]) + 
			") GROUP BY tname ORDER BY tname ASC)) INTERSECT (";
			
			String sql2 = 
				"SELECT tid, tname FROM ( " +
					"SELECT " +
						"N2G_TAGS.TAG_ID AS tid, " +
						"N2G_TAGS.TAG_NAME AS tname " +
					"FROM N2G_TAG2NOTE " +
					"LEFT JOIN N2G_TAGS ON N2G_TAG2NOTE.TAG_ID = N2G_TAGS.TAG_ID " +
					"WHERE (";
			for (int i = 0; i < labels.length - 1; i++) {
				sql += sql2 + "N2G_TAG2NOTE.TAG_ID != " + labels[i].getLabelID() +
				") GROUP BY tname ORDER BY tname ASC) " +
				"INTERSECT ";
			}
			
			sql += sql2 + "N2G_TAG2NOTE.TAG_ID != " + 
			labels[labels.length - 1].getLabelID() +
			") GROUP BY tname ORDER BY tname ASC)); ";
			selectTable(sql);
			
		}
		return dbResult;
	}
	
	public DBResult getEntryHeaders(Label[] labels){
		String sql = "";
		String sql1 = 
			"SELECT nid, ntitle, ndate FROM ( " +
				"SELECT " +
					"N2G_NOTE.NOTE_ID AS nid, " +
					"N2G_NOTE.NOTE_TITLE AS ntitle, " +
					"N2G_NOTE.NOTE_MODIFIED AS ndate " +
				"FROM N2G_TAG2NOTE " +
				"LEFT JOIN N2G_NOTE ON N2G_TAG2NOTE.NOTE_ID = N2G_NOTE.NOTE_ID " +
				"WHERE (";
		for (int i = 0; i < labels.length - 1; i++) {
			sql += sql1 + "N2G_TAG2NOTE.TAG_ID = " + labels[i].getLabelID() + 
				") GROUP BY nid ORDER BY ndate DESC) " +
				"INTERSECT ";
		}
		sql += sql1 + "N2G_TAG2NOTE.TAG_ID = " + labels[labels.length - 1].getLabelID() + 
		") GROUP BY nid ORDER BY ndate DESC)";
		selectTable(sql);
		return dbResult;
	}
	
	public DBResult getSearchResult(String q){
		dbResult = new DBResult();

		String[] qs = q.split(" ");
		String sql = 
				"SELECT DISTINCT N2G_NOTE.NOTE_ID, N2G_NOTE.NOTE_TITLE, N2G_NOTE.NOTE_MODIFIED " +
				"FROM N2G_NOTE, N2G_ENTRY WHERE ";
		for (int i = 0; i < qs.length; i++) {
			sql += "((UPPER(N2G_NOTE.NOTE_TITLE) LIKE UPPER('%" + qs[i] + "%') OR " +
				   "UPPER(N2G_ENTRY.ENTRY_CONTENT) LIKE UPPER('%" + qs[i] + "%')) AND " +
				   "N2G_NOTE.NOTE_ID = N2G_ENTRY.NOTE_ID";
			if (i == qs.length - 1){
				sql += ");";
			}else{
				sql += ") OR ";
			}
		}
		selectTable(sql);
		return dbResult;
	}
	
	public EntrySet setEntry(EntrySet entrySet){
		if(setN2GEntry(entrySet)){
			if(setN2GDataBlob()){
				if(setN2GCheckLabels()){
					if(setN2GNoteToLabel()){
						if(setN2GCheckUser2Note()){
							if(setN2GCheckGroup2Note()){
								
							}
						}
					}
				}
			}
		}
		return dbEntrySet;
	}
	
	public EntrySet getEntry(Integer id){
		
		dbEntrySet = new EntrySet();
		Object[][] result;
		Connection conn = getConnection();
		
		result = selectObjectTable("SELECT * FROM N2G_NOTE WHERE NOTE_ID=" + id + ";",conn);
		{
			if(result.length > 0){
				dbEntrySet.setEntryID((Integer) result[0][0]);
				dbEntrySet.setIsProtected((Boolean)result[0][2]);
				dbEntrySet.setIsPublic((Integer) result[0][3]);
				dbEntrySet.setEntryTitle((String) result[0][4]);
				dbEntrySet.setEntryAutor((String) result[0][6]);
				dbEntrySet.setEntryURL((String) result[0][7]);
				dbEntrySet.setDateCreated((Timestamp) result[0][8]);
				dbEntrySet.setDateModified((Timestamp) result[0][9]);
				dbEntrySet.setEntryType(TYPE.valueOf((String) result[0][10]));
			}
		}
		result = selectObjectTable("SELECT * FROM N2G_ENTRY WHERE NOTE_ID=" + id + ";",conn);
		{
			if(result.length > 0){
				Clob clob = (Clob) result[0][2];
				StringBuffer strOut = new StringBuffer();
				String line;
				try {
					BufferedReader br = new BufferedReader(clob.getCharacterStream());
					while ((line=br.readLine())!=null){
						strOut.append(line+"\n");
					}
					dbEntrySet.setEntryContent(strOut.toString());
				} catch (java.sql.SQLException e1) {
					e1.printStackTrace();
				} catch (java.io.IOException e2) {
					e2.printStackTrace();
				}
				
				Blob blob = (Blob) result[0][3];
				try {
					InputStream is = blob.getBinaryStream();
					ObjectInputStream istrm = new ObjectInputStream(is);
					dbEntrySet.setEntryStyledDocument((Document) istrm.readObject());
					istrm.close();
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
			}
			
		}
		
		result = selectObjectTable(
				"SELECT tid, tname FROM ( " +
				"SELECT " +
					"N2G_TAGS.TAG_ID AS tid, " +
					"N2G_TAGS.TAG_NAME AS tname " +
				"FROM N2G_TAG2NOTE " +
				"LEFT JOIN N2G_TAGS ON N2G_TAG2NOTE.TAG_ID = N2G_TAGS.TAG_ID " +
				"WHERE (" +
				"N2G_TAG2NOTE.NOTE_ID = " + dbEntrySet.getEntryID() +
				")  ORDER BY N2G_TAGS.TAG_NAME ASC) AS TAG_LIST;",conn);
		{
			if(result != null){
				Label[] tags = new Label[result.length];
				for (int i = 0; i < result.length; i++) {
					tags[i] = new Label(
							result[i][1].toString(),
							Integer.parseInt(result[i][0].toString()));
				}
				dbEntrySet.setEntryTags(tags);
			}
		}
		
		//TODO USER and GROUP import
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dbEntrySet;
	}
	
	public Boolean checkUser(User user){
		selectTable("SELECT pref_localpword FROM N2G_PREFERENCES " +
				"WHERE pref_ID=" + user.getUserID());
		String[][] result = dbResult.getRowContent();
		if(result[0][0].matches(user.getPassword().toString())){
			currUser = user;
			return true;
		}else{
			return false;
		}
	}
	
	public void deleteEntry(Integer id){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			statement.execute("DELETE FROM N2G_NOTE WHERE note_ID=" + id);
			conn.close();
		} catch (SQLException e) {
			// TODO: handle exception
		}
	}
	
	// ***************************************
	// *** PRIVATE METHODS *******************
	// ***************************************
	
	private void setConnectionPool(){
		String url = "jdbc:h2:" + dbURL + ";CIPHER=AES;IFEXISTS=TRUE";
		String  pword = filePassWord + " " + readPassWord;
		cp = JdbcConnectionPool.create(url, dbRoot, pword);
	}
	
	private Connection getConnection(){
		Connection conn = null;
		try {
			conn = cp.getConnection();
		} catch (JdbcSQLException e) {
			if (e.getErrorCode() == 90013)
				dbResult.isDBExists = false;
			else if (e.getErrorCode() == 8004)
				dbResult.isPassword = false;
			dbResult.setSqlComment(e.getLocalizedMessage());
		} catch (Exception e) {
			dbResult.isDBExists = false;
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
		return conn;
	}
	
	private void selectTable(String sql){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.execute("SET SCHEMA NOTE2GO;");
			ResultSet result = statement.executeQuery(sql);
			getTableHeader(result);
			getTableContent(result);
			conn.close();
		} catch (SQLException e) {
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}
	
	private void selectTable(String sql, Integer userID){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.execute("SET SCHEMA NOTE2GO;");
			statement.execute("SET @TEMPUID=" + userID + ";");
			ResultSet result = statement.executeQuery(sql);
			getTableHeader(result);
			getTableContent(result);
			conn.close();
		} catch (SQLException e) {
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}
	
	private void getTableHeader(ResultSet result){
        String temp = "";
        String tbHeader = "";
		try{
	        ResultSetMetaData resData = result.getMetaData();
	        int columnCount = resData.getColumnCount();
	        for (int i = 1; i <= columnCount; i++) {
	            // ... oder auf den Spaltennamen
	            temp = resData.getColumnName(i);
	            tbHeader += temp + ";";
	        }
		} catch (SQLException e){
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}

	
	private void getTableContent(ResultSet result) {
		String temp = "";
		String[][] tbContent = null;
		try {
			ResultSetMetaData resData = result.getMetaData();
			int colCount = resData.getColumnCount();
			int rowCount = 0;
			result.beforeFirst();
			while (result.next()) {
				rowCount++;
			}
			dbResult.setColumnCount(colCount);
			dbResult.setRowCount(rowCount);
			result.beforeFirst();
			tbContent = new String[rowCount][colCount];
			rowCount = 0;
			while (result.next()) {
				for (int i = 1; i <= colCount; i++) {
					temp = result.getString(i);
					if (temp == null) continue;
					tbContent[rowCount][i - 1] = temp;
				}
				rowCount++;
			}
			dbResult.setRowContent(tbContent);
		} catch (Exception e) {
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}
	
	private Object[][] selectObjectTable(String sql, Connection conn) {
		
		ResultSet result = null;
		Object temp = null;
		Object[][] tbContent = null;
		int rowCount, colCount;
		rowCount = colCount = 0;
		
		try {
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.execute("SET SCHEMA NOTE2GO;");
			result = statement.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			ResultSetMetaData resData = result.getMetaData();
			colCount = resData.getColumnCount();
			result.beforeFirst();
			while (result.next()) {
				rowCount++;
			}
			result.beforeFirst();
			tbContent = new Object[rowCount][colCount];
			rowCount = 0;
			while (result.next()) {
				for (int i = 1; i <= colCount; i++) {
					temp = result.getObject(i);
					if (temp == null) continue;
					tbContent[rowCount][i - 1] = temp;
				}
				rowCount++;
			}
			return tbContent;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private Boolean setPreferences(OptionsSet options){
		String sql;
		Integer localID,i;
		localID = i = 0;
		Connection conn = getConnection();

		if(options.getLocalUser().getUserID() == null){
			sql = "INSERT INTO N2G_PREFERENCES (" +
					"pref_userlocal,pref_localpword," +
					"pref_isroot,pref_isportable)" +
					"VALUES(?,?,?,?);";
		}else{
			localID = options.getLocalUser().getUserID();
			sql = "UPDATE N2G_PREFERENCES SET " +
			"pref_userlocal=?,pref_localpword=?," +
			"pref_isroot=?,pref_isportable=? " +
			"WHERE pref_ID=" + localID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(++i, options.getLocalUser().getName());
			prep.setString(++i, options.getLocalUser().getPassword().toString());
			prep.setBoolean(++i, options.getIsRoot());
			prep.setBoolean(++i, options.getIsPortable());
			prep.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	private boolean setNoteSettings(OptionsSet options) {

		String sql;
		Integer localID,newLocalID,i;
		localID = newLocalID = i = 0;
		Connection conn = getConnection();

		if(options.getLocalUser().getUserID() == null){
			selectTable("SELECT pref_ID FROM N2G_PREFERENCES " +
					"WHERE pref_userlocal='" +
					options.getLocalUser().getName() + "';");
			String[][] result = dbResult.getRowContent();
			newLocalID = Integer.parseInt(result[0][0]);
			sql = "INSERT INTO N2G_NOTESETTINGS (pref_ID)" +
					"VALUES(?);";
		}else{
			localID = options.getLocalUser().getUserID();
			sql = "UPDATE N2G_NOTESETTINGS SET " +
			"nset_public=?,nset_autosync=?,nset_synctime=?," +
			"nset_local2host=?,nset_host2local=? " +
			"WHERE pref_ID=" + localID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
        	if (localID == 0){
        		prep.setInt(++i, newLocalID);
        	}else{
				prep.setBoolean(++i, options.getNsetPublic());
				prep.setBoolean(++i, options.getNsetAutosync());
				prep.setInt(++i, options.getNsetSynctime());
				prep.setBoolean(++i, options.getNsetLocal2host());
				prep.setBoolean(++i, options.getNsetHost2local());
        	}
			prep.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}


	private boolean setServerSettings(OptionsSet options) {

		String sql;
		Integer localID,newLocalID,i;
		localID = newLocalID = i = 0;
		Connection conn = getConnection();

		if(options.getLocalUser().getUserID() == null){
			selectTable("SELECT pref_ID FROM N2G_PREFERENCES " +
					"WHERE pref_userlocal='" +
					options.getLocalUser().getName() + "';");
			String[][] result = dbResult.getRowContent();
			newLocalID = Integer.parseInt(result[0][0]);
			sql = "INSERT INTO N2G_SERVERSETTINGS (pref_ID)" +
					"VALUES(?);";
		}else{
			localID = options.getLocalUser().getUserID();
			sql = "UPDATE N2G_SERVERSETTINGS SET " +
			"server_name=?,server_port=?,server_hostid=?," +
			"server_nick=?,server_pword=?,server_autoconnect=? " +
			"WHERE pref_ID=" + localID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
        	if (localID == 0){
        		prep.setInt(++i, newLocalID);
        	}else{
				prep.setString(++i, options.getServerName());
				prep.setInt(++i, options.getServerPort());
				prep.setInt(++i, options.getHostUser().getUserID());
				prep.setString(++i, options.getHostUser().getName());
				prep.setString(++i, options.getHostUser().getPassword().toString());
				prep.setBoolean(++i, options.getServerAutoConnect());
        	}
			prep.execute();
			conn.close();
			
			currUser = new User(options.getLocalUser().getName(),newLocalID);
			unsortLabel = new Label("unsortiert",currUser.getUserID());
			if(options.getLocalUser().getUserID() == null){
				firstRunSettings();
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	private boolean setUserSettings(OptionsSet options) {
		// TODO Auto-generated method stub
		return true;
	}


	private boolean setGroupSettings(OptionsSet options) {
		// TODO Auto-generated method stub
		return true;
	}


	private boolean setUser2Group(OptionsSet options) {
		// TODO Auto-generated method stub
		return true;
	}

	
	private void firstRunSettings(){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			statement.execute(
					"INERT INTO N2G_TAGS (tag_name,tag_owner) VALUES " +
					"('"+ currUser.getName() + "'," + currUser.getUserID() + ");");
			conn.close();
			
		} catch (SQLException e) {
			// TODO: handle exception
		}
		
	}


	private Boolean setN2GEntry(EntrySet entrySet){

		this.dbEntrySet = entrySet;
		String sql;
		Integer localID,i,entryID;
		i = 0;
		entryID = dbEntrySet.getEntryID();
		localID = currUser.getUserID();

		Connection conn = getConnection();
		Timestamp timeStamp = new java.sql.Timestamp(new java.util.Date().getTime());

		if(entryID == null){
			sql = "INSERT INTO N2G_NOTE (" +
					"pref_ID,note_create,note_protected," +
					"note_public,note_title,note_author," +
					"note_type,note_modified)" +
					"VALUES(?,?,?,?,?,?,?,?);";
			dbEntrySet.setEntryAutor(currUser.getName());
		}else{
			sql = "UPDATE N2G_NOTE SET " +
			"note_protected=?,note_public=?," +
			"note_title=?,note_author=?," +
			"note_type=?,note_modified=? " +
			"WHERE note_ID=" + entryID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
        	if (dbEntrySet.getEntryID() == null){
        		prep.setInt(++i, localID);
        		prep.setTimestamp(++i, timeStamp);
        	}
        	prep.setBoolean(++i, dbEntrySet.getIsProtected());
			prep.setInt(++i, dbEntrySet.getIsPublic());
			prep.setString(++i, dbEntrySet.getEntryTitle());
			prep.setString(++i, dbEntrySet.getEntryAutor());
			prep.setString(++i, dbEntrySet.getEntryType());
			prep.setTimestamp(++i, timeStamp);
			prep.execute();
			conn.close();
			
			if (dbEntrySet.getEntryID() == null){
				selectTable("SELECT MAX(note_ID) FROM N2G_NOTE WHERE pref_ID=" + localID);
				String[][] result = dbResult.getRowContent();
				dbEntrySet.setEntryID(Integer.parseInt(result[0][0]));
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	private boolean setN2GDataBlob(){
		
		String sql;
		Integer i, blobID;
		i = blobID = 0;

		Connection conn = getConnection();
		
		selectTable("SELECT entry_ID FROM N2G_ENTRY WHERE note_ID=" + dbEntrySet.getEntryID());
		String[][] result = dbResult.getRowContent();
		if(result.length > 0)
			blobID = Integer.parseInt(result[0][0]);
		
		if(blobID <= 0){
			sql = "INSERT INTO N2G_ENTRY (" +
					"note_ID,entry_content,entry_blob) " +
					"VALUES(?,?,?);";
		}else{
			sql = "UPDATE N2G_ENTRY SET " +
			"entry_content=?,entry_blob=? " +
			"WHERE entry_ID=" + blobID + ";";
		}
		
        try {
        	File docFile = File.createTempFile("doc", "tmp");
			ObjectOutputStream ostrm = new ObjectOutputStream(
					new FileOutputStream(docFile.getCanonicalPath()));
			ostrm.writeObject(dbEntrySet.getEntryStyledDocument());
			ostrm.flush();
			ostrm.close();
			
			InputStream clobData = new ByteArrayInputStream(dbEntrySet.getEntryContent().getBytes());
			InputStream blobData = new FileInputStream(docFile); 
			
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA NOTE2GO;");
			
			PreparedStatement prep = conn.prepareStatement(sql);
	    	if (blobID <= 0){
	    		prep.setInt(++i, dbEntrySet.getEntryID());
	    	}
	    	prep.setAsciiStream(++i, clobData);
			prep.setBinaryStream(++i, blobData); 

			prep.execute();
			conn.close();
			
			clobData.close();
			blobData.close();
			docFile.delete();
			
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}
	}


	private boolean setN2GCheckLabels() {
		Label[] labels = dbEntrySet.getEntryTags();
		if(labels.length < 1){
			labels = new Label[]{unsortLabel};
		}
		for (Label label : labels) {
			selectTable(
					"SELECT TAG_ID FROM N2G_TAGS WHERE UPPER(TAG_NAME)='" + 
					label.getName().toUpperCase() + "';");
			if(dbResult.getRowContent().length < 1){
				Connection conn = getConnection();
				try {
					statement = conn.createStatement();
					statement.execute("SET SCHEMA NOTE2GO;");
					statement.execute("INSERT INTO N2G_TAGS (TAG_NAME, TAG_OWNER) " +
							"VALUES('"+ 
								label.getName() +"', "+
								currUser.getUserID() +");");
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				selectTable(
						"SELECT TAG_ID FROM N2G_TAGS WHERE UPPER(TAG_NAME)='" + 
						label.getName().toUpperCase() + "';");
				String newID[][] = dbResult.getRowContent();
				label.setLabelID(Integer.parseInt(newID[0][0]));
			}
		}
		dbEntrySet.setEntryTags(labels);
		return true;
	}


	private boolean setN2GNoteToLabel() {
		Label[] labels = dbEntrySet.getEntryTags();
		
		for (Label label : labels) {
			if(labels.length > 1){
				if(label.getName().matches("unsortiert")){
					Connection conn = getConnection();
					try {
						statement = conn.createStatement();
						statement.execute("SET SCHEMA NOTE2GO;");
						statement.execute("DELETE FROM N2G_TAG2NOTE WHERE " +
							"NOTE_ID=" + dbEntrySet.getEntryID() + " AND " +
							"TAG_ID=" + label.getLabelID() + ";");
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					continue;
				}
				
			}
			selectTable(
					"SELECT TN_ID FROM N2G_TAG2NOTE WHERE " +
					"NOTE_ID=" + dbEntrySet.getEntryID() + " AND " +
					"TAG_ID=" + label.getLabelID() + ";");
			if(dbResult.getRowContent().length < 1){
				Connection conn = getConnection();
				try {
					statement = conn.createStatement();
					statement.execute("SET SCHEMA NOTE2GO;");
					statement.execute(
							"INSERT INTO N2G_TAG2NOTE (PREF_ID, NOTE_ID, TAG_ID) VALUES(" +
							currUser.getUserID() +","+ 
							dbEntrySet.getEntryID() +","+ 
							label.getLabelID() +");");
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}


	private boolean setN2GCheckUser2Note() {
		User[] users = dbEntrySet.getPrivateUsers();
		if(users.length < 1){
			return true;
		}else{
			for (User user : users) {
				selectTable(
						"SELECT UNLIST_ID FROM N2G_USER2NOTE WHERE " +
						"NOTE_ID=" + dbEntrySet.getEntryID() + " AND " +
						"ULIST_ID=" + user.getUserID() + ";");
				if(dbResult.getRowContent().length < 1){
					Connection conn = getConnection();
					try {
						statement = conn.createStatement();
						statement.execute("SET SCHEMA NOTE2GO;");
						statement.execute(
								"INSERT INTO N2G_USER2NOTE (PREF_ID, NOTE_ID, ULIST_ID) VALUES(" +
								currUser.getUserID() +","+ 
								dbEntrySet.getEntryID() +","+ 
								user.getUserID() +");");
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return true;
	}


	private boolean setN2GCheckGroup2Note() {
		User[] groups = dbEntrySet.getPrivateGroups();
		if(groups.length < 1){
			return true;
		}else{
			for (User group : groups) {
				selectTable(
						"SELECT GNLIST_ID FROM N2G_GROUP2NOTE WHERE " +
						"NOTE_ID=" + dbEntrySet.getEntryID() + " AND " +
						"GLIST_ID=" + group.getUserID() + ";");
				if(dbResult.getRowContent().length < 1){
					Connection conn = getConnection();
					try {
						statement = conn.createStatement();
						statement.execute("SET SCHEMA NOTE2GO;");
						statement.execute(
								"INSERT INTO N2G_GROUP2NOTE (PREF_ID, NOTE_ID, GLIST_ID) VALUES(" +
								currUser.getUserID() +","+ 
								dbEntrySet.getEntryID() +","+ 
								group.getUserID() +");");
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return true;
	}
	
	
    private String readTextFile(String filename){
    	String line;
    	String sqlQuery = "";
		try
		{
			InputStream fis = getClass().getResourceAsStream(filename);
			BufferedReader reader=new BufferedReader ( new InputStreamReader(fis));
			while((line = reader.readLine()) != null)
			{
				sqlQuery += line +"\n";
			}
			reader.close();
		}
		catch (Exception e) {}
		return sqlQuery;
    }



}
